// Copyright (c) Microsoft Corporation. All rights reserved.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Transactions;
using System.Data;
using System.Globalization;
using System.Data.Common;
using System.Reflection;

namespace Microsoft.TeamFoundation.Migration.Toolkit
{
    public delegate T DataReaderRealizer<T>(SqlDataReader reader);

    /// <summary>
    /// The class through which toolkit SQL operations are performed.
    /// </summary>
    public class DataAccessManager
    {
        /// <summary>
        /// Database schema type.
        /// </summary>
        [Flags]
        public enum SchemaType
        {
            All = VC | WorkItemTracking | Linking,

            VC = 0x01,                      // Version control schema
            WorkItemTracking = 0x02,        // Work item tracking schema
            Linking = 0x04,                 // Deferred links table
        }

        private static void Initialize()
        {
            current = new DataAccessManager();
        }

        /// <summary>
        /// Returns a SqlConnection object using the application defined connection
        /// string.
        /// </summary>
        /// <returns>An allocated SqlConnection object</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate")]
        public SqlConnection GetSqlConnection()
        {
            // this will end up using ADO.NET's underlying connection
            // pooling http://msdn2.microsoft.com/en-US/library/8xx3tyca.aspx
            return new SqlConnection(ConnectionString);
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")]
        public IMigrationTransaction StartTransaction()
        {
            return new MigrationSqlTransaction();
        }


        #region ExecuteNonQuery
        /// <summary>
        /// Executes a SqlCommand object using a pooled connection and 
        /// returns the number of affected rows.
        /// The query is not executed under a transaction.
        /// </summary>
        /// <param name="command">The command to Execute</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(DbCommand command)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            return command.ExecuteNonQuery();
        }

        #endregion

        /// <summary>
        /// Executes a SQL command and sets the result parameter to the first column of
        /// the first row returned (scalar execution).
        /// </summary>
        /// <typeparam name="T">The return type of the scalar value</typeparam>
        /// <param name="command">The command to Execute</param>
        /// <param name="result">The returned value from the scalar query</param>
        /// <returns>True if the scalar value was returned, false otherwise.</returns>
        public static bool TryExecuteScalar<T>(DbCommand command, out T result)
            where T : IConvertible
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            result = default(T);

            object objResult = command.ExecuteScalar();

            bool success = false;
            if (objResult != null)
            {
                result = (T)Convert.ChangeType(objResult, typeof(T), CultureInfo.InvariantCulture);
                success = true;
            }

            return success;
        }

        /// <summary>
        /// Returns the configuration defined SQL connection string or throws an 
        /// MigrationException if the configuration setting is missing.
        /// </summary>
        protected virtual string ConnectionString
        {
            get
            {
                return MigrationConfiguration.Sql.ConnectionString;
            }
        }

        /// <summary>
        /// The singleton instance of the DataAccessMananger.
        /// </summary>
        public static DataAccessManager Current
        {
            get
            {
                if (current == null)
                {
                    lock (typeof(DataAccessManager))
                    {
                        if (current == null)
                        {
                            Initialize();
                        }
                    }
                }

                return current;
            }
        }

        static DataAccessManager current;

        /// <summary>
        /// Executes a SQL operation and iterates calls the provided callback with the resulting reader.
        /// </summary>
        /// <typeparam name="T">The type the callback will return.</typeparam>
        /// <param name="command">The SQL command to execute</param>
        /// <param name="realizer">The callback method to invoke with the SqlDataReader</param>
        /// <returns>The result of the callback method (realizer)</returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
        public static T ExecuteReader<T>(SqlCommand command, 
            DataReaderRealizer<T> realizer)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }

            if (realizer == null)
            {
                throw new ArgumentNullException("realizer");
            }

            using (SqlDataReader reader = command.ExecuteReader())
            {
                return realizer(reader);
            }
        }

        /// <summary>
        /// Creates the Migration Toolkit SQL schema. 
        /// </summary>
        /// <param name="overwrite">Overwrite existing schema flag</param>
        public void CreateSchema(bool overwrite)
        {
            CreateSchema(overwrite, SchemaType.All);
        }

        /// <summary>
        /// Creates the Migration Toolkit SQL schema.  This includes the SQL database specified in the migration configuration,
        /// and the toolkit tables and stored procedures.  If the database exists and overwrite is false the existing database will
        /// be used and no changes will occur.  If the database does not exist or if overwrite is true the database will be created.
        /// If the database exists and overwrite is true the existing database and all of it's assocaited data will be dropped.
        /// </summary>
        /// <param name="overwrite">If true the existing database, if any, will be dropped before the schema is created.  If false any existing database will be used (and left unaltered).</param>
        /// <param name="type">Defines what parts of the schema will be created</param>
        public void CreateSchema(bool overwrite, SchemaType type)
        {
            SqlConnection.ClearAllPools();

            // do the work in master since we know we can connect here
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
            builder.InitialCatalog = "master";

            string dbName = MigrationConfiguration.Sql.Database;

            // Create a connection to the master database (since the database we'll Create may not exist)
            using (SqlConnection conn = new SqlConnection(builder.ToString()))
            {
                conn.Open();
                bool dbExists = SqlUtil.DoesDbExist(conn, dbName);

                if (dbExists && overwrite)
                {
                    SqlUtil.DropDb(conn, dbName);
                }

                if (!dbExists || overwrite)
                {
                    SqlUtil.CreateDB(conn, dbName);
                    overwrite = true;
                }
            }

            if (overwrite)
            {
                // Create a new connection - this time to the real database
                using (SqlConnection conn = GetSqlConnection())
                {
                    createTables(conn, type);
                    createSprocs(conn, type);
                }
            }
        }

        private static void createSprocs(SqlConnection conn, SchemaType type)
        {
            if ((type & SchemaType.VC) != 0)
                SqlUtil.ExecuteNamedResource("Microsoft.TeamFoundation.Migration.Toolkit.VC.SQL.MigrationSprocs.sql", conn);
            if ((type & SchemaType.WorkItemTracking) != 0)
                SqlUtil.ExecuteNamedResource("Microsoft.TeamFoundation.Migration.Toolkit.Wit.SQL.MigrationSprocs.sql", conn);
            if ((type & SchemaType.Linking) != 0)
                SqlUtil.ExecuteNamedResource("Microsoft.TeamFoundation.Migration.Toolkit.Linking.SQL.MigrationSprocs.sql", conn);
        }

        private static void createTables(SqlConnection conn, SchemaType type)
        {
            if ((type & SchemaType.VC) != 0)
                SqlUtil.ExecuteNamedResource("Microsoft.TeamFoundation.Migration.Toolkit.VC.SQL.MigrationDatabase.sql", conn);
            if ((type & SchemaType.WorkItemTracking) != 0)
                SqlUtil.ExecuteNamedResource("Microsoft.TeamFoundation.Migration.Toolkit.Wit.SQL.MigrationDatabase.sql", conn);
            if ((type & SchemaType.Linking) != 0)
                SqlUtil.ExecuteNamedResource("Microsoft.TeamFoundation.Migration.Toolkit.Linking.SQL.MigrationDatabase.sql", conn);
        }
    }
}
